package com.tongji.run;

import com.tongji.entity.宣传员;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LuFeiTongJi {
    static Logger logger = LoggerFactory.getLogger(LuFeiTongJi.class);
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";

    static final String DB_URL = "jdbc:mysql://localhost:3308/tongji?serverTimezone=GMT&characterEncoding=utf-8";

    //  Database credentials
    static final String USER = "root";

    static final String PASS = "123456";

    public static Connection conn = null;

    public static void main(String[] args) {

//
//        LuFeiTongJi luFeiTongJi = new LuFeiTongJi();
//        List xcyList = luFeiTongJi.得到宣传员列表();
//        System.out.println(xcyList);
    }

    public Connection initConn() {
        logger.info("STEP 2: Register JDBC driver");
        Class driver_class = null;
        Driver driver = null;
        try {
            driver_class = Class.forName(JDBC_DRIVER);
            driver = (Driver)driver_class.newInstance();
            DriverManager.registerDriver(driver);
            logger.info("STEP 3:Open a connection");
            logger.info("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public List<String> 得到宣传员列表() {
        initConn();
        List xcyList = new ArrayList();
        Statement stmt = null;
        try
        {
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM 路费统计宣传员";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next())
            {
                宣传员 xcy = new 宣传员();
                String name = rs.getString("name");
                xcy.setName(name);
                xcyList.add(xcy);
            }
            rs.close();
            stmt.close();
            conn.close();
        }
        catch (SQLException se)
        {
            se.printStackTrace();
        }
        catch (Exception e)
        {
            //Handle errors for Class.forName
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (stmt != null)
                    stmt.close();
            }
            catch (SQLException se2)
            {
            }// nothing we can do
            try
            {
                if (conn != null)
                    conn.close();
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }//end finally try
        }//end try
        return xcyList;
    }
}
